﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Configuration;

namespace DAO
{
    public class RoomType
    {
        private int capacity;

        public int Capacity
        {
            get { return capacity; }
            set { capacity = value; }
        }
        private string name;

        public string Name
        {
            get { return name; }
            set { name = value; }
        }
        private decimal prise;

        public decimal Prise
        {
            get { return prise; }
            set { prise = value; }
        }
        private int id;

        public int Id
        {
            get { return id; }
            set { id = value; }
        }

        public static RoomType GetByID(int id)
        {
            string str = "Data Source=(local)\\sqlexpress;Initial Catalog=Hotel System;Integrated Security=True";
            string commandString =
                " select * from RoomType where RoomTypeID=@id";
            SqlParameter pId = new SqlParameter("@id", SqlDbType.Int);
            pId.Value = id;
            RoomType type = new RoomType();
            SqlConnection conn = new SqlConnection(str);
            conn.Open();
            try
            {
                SqlCommand commandSelect = new SqlCommand(commandString);
                commandSelect.Connection = conn;
                commandSelect.Parameters.Add(pId);
                SqlDataReader reader = commandSelect.ExecuteReader(); ;
                using (reader)
                {
                    while (reader.Read())
                    {

                        type.Name = (string)reader["Name"];
                        type.Prise = (decimal)reader["Prise"];
                        type.Capacity = (int)reader["Capacity"];
                        type.id = (int)reader["RoomTypeID"];

                    }
                }
                return type;

            }
            finally
            {
                conn.Close();
            }
        }


        public static void Insert(string name, int capacity, decimal prize)
        {
            string str = "Data Source=(local)\\sqlexpress;Initial Catalog=Hotel System;Integrated Security=True";
            string commandString =
                " insert into RoomType([Name],Capacity,Prise) Values (@name,@capacity,@price)";

            SqlConnection conn = new SqlConnection(str);
            conn.Open();
            try
            {
                SqlParameter pName = new SqlParameter("@name", SqlDbType.NChar);
                SqlParameter pCapacity = new SqlParameter("@capacity", SqlDbType.Int);
                SqlParameter pPrice = new SqlParameter("@price", SqlDbType.Money);
                pName.Value =name;
                pCapacity.Value = capacity;
                pPrice.Value = prize;

                SqlCommand commandInsert = new SqlCommand(commandString);
                commandInsert.Parameters.Add(pName);
                commandInsert.Parameters.Add(pCapacity);
                commandInsert.Parameters.Add(pPrice);
                commandInsert.ExecuteNonQuery();
            }
            finally { conn.Close(); }
        }

        public static void Change(int id,string name, int capacity, decimal price)
        {
            string str = "Data Source=(local)\\sqlexpress;Initial Catalog=Hotel System;Integrated Security=True";
            string commandString =
                "update RoomType SET Name=@name, Capacity=@capacity, Prise=@price from RoomType where RoomTypeID=@id";
            SqlConnection conn = new SqlConnection(str);
            conn.Open();
            try
            {

                SqlParameter cap = new SqlParameter("@capacity", SqlDbType.Int);
                SqlParameter pPrice = new SqlParameter("@price", SqlDbType.Decimal);
                SqlParameter pName = new SqlParameter("@name", SqlDbType.NChar);
                SqlParameter pID = new SqlParameter("@id", SqlDbType.Int);
                pID.Value = id;
                cap.Value = capacity;
                pPrice.Value = price;
                pName.Value = name;
                
                SqlCommand commandUpdate = new SqlCommand(commandString);
                commandUpdate.Connection = conn;
                commandUpdate.Parameters.Add(cap);
                commandUpdate.Parameters.Add(pPrice);
                commandUpdate.Parameters.Add(pName);
                commandUpdate.Parameters.Add(pID);
                commandUpdate.ExecuteNonQuery();
            }
            finally { conn.Close(); }       
        }
       

        public static RoomType[] Get()
        {
            string str = "Data Source=(local)\\sqlexpress;Initial Catalog=Hotel System;Integrated Security=True";
            string commandString =
                " select * from RoomType";
            List<RoomType> result = new List<RoomType>();
            SqlConnection conn = new SqlConnection(str);
            conn.Open();
            try
            {
                SqlCommand commandSelect = new SqlCommand(commandString);
                commandSelect.Connection = conn;
               SqlDataReader reader= commandSelect.ExecuteReader(); ;
                using (reader)
                {
                    while (reader.Read())
                    {
                        RoomType type = new RoomType();
                        type.Name = (string)reader["Name"];
                        type.Prise = (decimal)reader["Prise"];
                        type.Capacity = (int)reader["Capacity"];
                        type.id = (int)reader["RoomTypeID"];
                        result.Add(type);
                    }
                }
                return result.ToArray();

                

            }
            finally { conn.Close(); }
            
        }
    }
}
